package db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Logger;

import util.MyLogger;

/**
 * This class provides the basic connection to the database. The database will
 * be started the first time a {@link DBObject} was created. The method
 * DBConnection.shutdown() will shut down it correct and clean up. It is
 * recommend to use it!
 * 
 * @author Kristin Behrens, Robin Gawenda, Swen Jung
 */
public class DBConnection extends MyLogger {
	/**
	 * Connection represents the interface to database. All operations are run
	 * via this object.
	 */
	private static Connection connection = null;

	/**
	 * Creates an DBConnection object and a database connection with the given
	 * driver and via given bridge.
	 * 
	 * @throws ClassNotFoundException -
	 *             thrown if the driver could not be found.
	 * @throws SQLException -
	 *             thrown if the connection attempt failed.
	 */
	private DBConnection() throws ClassNotFoundException, SQLException {
		super();
	}

	/**
	 * Returns a {@link Connection} object for database connection created by
	 * this object.
	 * 
	 * @return a {@link Connection} object for database connectivity.
	 * @throws ClassNotFoundException 
	 * @throws SQLException 
	 */
	public static Connection getConnection() throws ClassNotFoundException, SQLException {
		if (connection == null) {
			if(log==null)new DBConnection();
			Class.forName("org.hsqldb.jdbcDriver");
			String file="./config/mydb";
			connection = DriverManager.getConnection("jdbc:hsqldb:file:"+file, "sa", "");
			log.info("Database is running");
		}
		return connection;
	}
	
	protected static void checkUsers(){
		try {
			if(getConnection().createStatement().execute("CREATE USER my_user_std PASSWORD \'\'; " +
					"CREATE USER moderator PASSWORD \'\'; CREATE USER admin PASSWORD \'\';"))
				log.info("The following login roles user_std, moderator and admin have been created.");
			else log.warning("The following login roles user_std, moderator and admin could not be" +
					"created. This is whether due to preexisting roles or an error has occured.");
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	protected static void killUsers(){
		try {
			if(getConnection().createStatement().execute("DROP USER my_user_std; " +
					"DROP USER moderator; DROP USER admin; DROP USER user_std;"))
				log.info("The following login roles user_std, moderator and admin have been created.");
			else log.warning("The following login roles user_std, moderator and admin could not be" +
					"created. This is whether due to preexisting roles or an error has occured.");
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Returns if connection is already shutdown.
	 * 
	 * @return boolean
	 */
	public static boolean isShutDown() {
		try {
			return connection.isClosed();
		} catch (SQLException e) {
			Logger.getLogger("db.DBConnection").throwing("DBConnection",
					"shutdown", e);
		}
		return false;
	}

	/**
	 * Cleans up the database and shuts it down.
	 * 
	 * @throws SQLException
	 */
	public static void shutdown() {
		try {
			Statement st = connection.createStatement();
			st.execute("SHUTDOWN");
			// if there are no other open connection
			connection.close();
			Logger.getLogger("db.DBConnection").fine("Database is shutdown");
		} catch (SQLException e) {
			Logger.getLogger("db.DBConnection").throwing("DBConnection",
					"shutdown", e);
		}
	}
}
